const router = require('koa-router')()
const svgCaptcha = require('svg-captcha')
const fs = require('fs')
const path = require('path')
const jwt = require('jsonwebtoken');
const tokenSecret = 'mysecretkey';
const filePath = path.join(__dirname, '../public/images')
const pool = require('./mydb')

router.get('/ok', async (ctx, next) => {
  ctx.body = 'ok'
})

router.get('/', async (ctx, next) => {
  await ctx.render('index', {
    title: 'Hello Koa 2!'
  })
})

router.post('/getUser', async (ctx, next) => {
  // let list = [{"username": "zhangsan", "pwd": "123456"},{"username":"lisi","pwd":"1234"}]; 
  const [rows, fields] = await pool.query('SELECT * FROM login');
  let username = ctx.request.body.username;
  let pwd = ctx.request.body.pwd;
  let captcha = ctx.request.body.captcha.toLowerCase();
  let isAccount = rows.filter(e => e.username == username)[0]
  if (captcha === ctx.session.captcha) {
    if (!isAccount) {
      ctx.body = { msg: '账户不存在', token: null }
    } else {
      if (isAccount.pwd == pwd) {
        const token = jwt.sign({ username: username }, tokenSecret, { expiresIn: 60 });
        ctx.session.token = token;
        ctx.body = { msg: '登录成功', token: token };
      } else {
        ctx.body = { msg: '密码错误', token: null }
      }
    }
  } else {
    ctx.body = { msg: '验证码错误', token: null };
  }
})

router.get('/captcha', async (ctx, next) => {
  let captcha = svgCaptcha.create({
    size: 4,
    noise: 2,
    color: true,
    background: "#666",
    width: 123,
    height: 40,
    ignoreChars: '0iloOLI'
  });
  let img = captcha.data;
  let text = captcha.text.toLowerCase();
  ctx.session.captcha = text;
  ctx.response.type = 'image/svg+xml';
  ctx.body = { "img": img, "text": text };
})

// 文件上传
router.post('/upload', async (ctx, next) => {
  const token = ctx.header.authorization
  if (!token || !ctx.session.token) {
    ctx.body = {
      message: 'token missed'
    }
  } else {
    const files = ctx.request.files.files
    let context = []
    if (files instanceof Array) {
      context = files
    } else {
      context.push(files)
    }
    try {
      for (let file of context) {
        const reader = fs.createReadStream(`${file.filepath}`)
        let filePaths = path.join(__dirname, '../public/images/', `${file.originalFilename}`)
        const upStream = fs.createWriteStream(`${filePaths}`)
        reader.pipe(upStream)
        pool.query(`INSERT INTO picture ( name, type, size) VALUES 
        ('${file.originalFilename}','${file.mimetype}',${file.size})`)
      }
      ctx.body = { code: 200 }
    } catch (error) {
      ctx.body = { msg: error }
    }
  }
});

router.get('/files', async (ctx, next) => {
  const [rows, fields] = await pool.query('SELECT * FROM picture');
  rows.map(item => {
    item.path = `http://localhost:3000/images/${item.name}`
  })
  ctx.body = rows;
});

router.delete("/del", async (ctx) => {
  const id = ctx.request.query.id;
  const name = ctx.request.query.name;
  await pool.query(`DELETE FROM picture WHERE id=${id};`);
  const [rows, fields] = await pool.query(`SELECT * FROM picture where name='${name}'`);
  if (rows.length == 0) {
    const filepaths = `${filePath}/${name}`;
    await fs.promises.unlink(filepaths);
  }
  ctx.body = id;
});

router.get('/user', async (ctx, next) => {
  const [rows, fields] = await pool.query('SELECT * FROM user');
  ctx.body = rows;
});

router.post('/addUser', async (ctx, next) => {
  let temp = ctx.request.body
  await pool.query(`INSERT INTO user ( name, telephone, email, address) VALUES 
  (${temp.name},${temp.telephone},${temp.email},${temp.address})`)
  ctx.body = { code: 200 };
});

router.post('/alterUser', async (ctx, next) => {
  let temp = ctx.request.body
  await pool.query(`UPDATE user
  SET name='${temp.name}',telephone='${temp.telephone}',email='${temp.email}',address='${temp.address}'
  WHERE id=${temp.index} `)
  ctx.body = { code: 200 }
});

router.post('/searchUser', async (ctx, next) => {
  let temp = ctx.request.body
  const [rows, fields] = await pool.query
    (`SELECT * FROM user where name like '%${temp.key}%' or telephone like '%${temp.key}%' 
    or email like '%${temp.key}%' or address like '%${temp.key}%'`);
  ctx.body = rows
})

router.delete('/deleteUser', async (ctx, next) => {
  const item = ctx.request.query.deleteIndex;
  await pool.query(`DELETE FROM user WHERE id=${item};`);
  ctx.body = { code: 200 }
})


router.get('/line', async (ctx, next) => {
  const [rows, fields] = await pool.query('SELECT * FROM line order by seq desc');
  ctx.body = rows;
});

router.post('/addLine', async (ctx, next) => {
  let temp = ctx.request.body
  await pool.query(`INSERT INTO line ( name, road, address ,telephone,seq) VALUES 
  (${temp.name},${temp.road},${temp.address},${temp.telephone},0)`)
  ctx.body = { code: 200 };
});

router.post('/alterLine', async (ctx, next) => {
  let temp = ctx.request.body
  await pool.query(`UPDATE line
  SET name='${temp.name}',road='${temp.road}',address='${temp.address}',telephone='${temp.telephone}'
  WHERE id=${temp.index} `)
  ctx.body = { code: 200 }
});

router.post('/topLine', async (ctx, next) => {
  let temp = ctx.request.body
  await pool.query(`UPDATE line SET seq=1 WHERE id=${temp.id}`)
  ctx.body = temp;
});

router.post('/searchLine', async (ctx, next) => {
  let temp = ctx.request.body
  const [rows, fields] = await pool.query
    (`SELECT * FROM line where name like '%${temp.key}%' or telephone like '%${temp.key}%' 
    or road like '%${temp.key}%' or address like '%${temp.key}%'`);
  ctx.body = rows
})


router.delete('/deleteLine', async (ctx, next) => {
  const item = ctx.request.query.deleteIndex;
  await pool.query(`DELETE FROM line WHERE id=${item};`);
  ctx.body = { code: 200 }
})

module.exports = router